﻿Imports DTO
Imports System.Data.SqlClient
Public Class HoaDonDAO

    'Thêm hóa đơn

    Public Sub Them(ByVal hddto As HoaDonDTO)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Insert into HOADON(MaKachHang,NgayLap) values(@MaKachHang,@NgayLap)"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)

        cmd.Parameters.Add("@MaKachHang", SqlDbType.Int)
        cmd.Parameters("@MaKachHang").Value = hddto.MaKhachHang

        cmd.Parameters.Add("@NgayLap", SqlDbType.DateTime)
        cmd.Parameters("@NgayLap").Value = hddto.NgayLap


        cmd.ExecuteNonQuery()


        strSQL = "Select @@IDENTITY"
        cmd = New SqlCommand(strSQL, cn)
        hddto.MaHoaDon = Convert.ToInt32(cmd.ExecuteScalar())

        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Function LayBang() As DataTable
        Dim dt As New DataTable()
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From HoaDon"
        'B4: Thuc thi chuoi strSQL
        Dim da As New SqlDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function

    Public Function LayDanhSach() As List(Of HoaDonDTO)
        Dim ds As New List(Of HoaDonDTO)
        Dim cn As SqlConnection
        Dim strSQL As String
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        strSQL = "Select * From HoaDon"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader()
        Dim hoadon As New HoaDonDTO()
        While (dr.Read())
            hoadon = New HoaDonDTO()
            hoadon.MaHoaDon = dr("MaHoaDon")
            ds.Add(hoadon)
        End While
        'B5: Dong ket noi CSDL
        cn.Close()
        Return ds
    End Function
End Class
